package com.chinasoft.mapper;


import com.chinasoft.pojo.GoodCount;
import com.chinasoft.pojo.TeacherCount;
import com.chinasoft.pojo.TeacherList;
import com.chinasoft.pojo.Userlist;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface UserlistMapper {
    @Insert("insert into userlist (name,username,teachername,teacher,classid,picture,classname,classtype,money,usertools,time,paytime) " +
            " values (#{name},#{username},#{teachername},#{teacher},#{classid}, " +
            " #{picture},#{classname},#{classtype},#{money},#{usertools},#{time},#{paytime}) ")
    int insert(Userlist userlist);

    @Select("SELECT * FROM userlist WHERE username = #{username}")
    List<Userlist> getById(String username);

    @Select("SELECT COUNT(*) FROM userlist WHERE username = #{username} AND classid = #{classid} AND del = #{del}")
    Integer getclass(@Param("classid") Integer classid,@Param("username") String username ,@Param("del") String del);

    @Select("SELECT COUNT(*) FROM userlist WHERE username = #{username} AND classtype = #{classtype} AND time = #{time}")
    Integer getBytime(@Param("time") String time,@Param("classtype") String classtype,@Param("username") String username);

    @Select("SELECT * FROM userlist WHERE classid = #{classid} AND del =#{del}")
    List<Userlist> getId(@Param("classid") Integer classid, @Param("del") String del);

    @Delete("delete from userlist where id = #{id}")
    int delete(Integer id);

    @Select("SELECT classid FROM userlist WHERE id = #{id}")
    Integer find1(Integer id);

    @Update("UPDATE userlist SET del = #{del} WHERE id = #{id}")
    int updateDel(@Param("id") Integer id, @Param("del")String del);

    @Select("<script> SELECT * from userlist" +
            "<where>"+
            "<if test='classname != null'>and classname like concat('%',#{classname},'%') </if> "+
            " <if test='username != null'>and username = #{username} </if> " +
            "</where>"+
            "</script>")
    List<Userlist> findBy(Userlist list);

    @Select("SELECT * FROM userlist WHERE username = #{username} and del = #{del}")
    List<Userlist> search(@Param("username")String username, @Param("del")String del);

    @Select("SELECT DATE_FORMAT(STR_TO_DATE(userlist.paytime, '%Y/%m/%d %H:%i'), '%Y/%m') AS month, " +
            " SUM(IFNULL(userlist.money, 0) + IFNULL(usergood.startime, 0)) AS totalAmount " +
            " FROM userlist " +
            " LEFT JOIN usergood " +
            " ON DATE_FORMAT(STR_TO_DATE(userlist.paytime, '%Y/%m/%d %H:%i'), '%Y/%m') = " +
            " DATE_FORMAT(STR_TO_DATE(usergood.startime, '%Y/%m/%d %H:%i'), '%Y/%m') " +
            " GROUP BY DATE_FORMAT(STR_TO_DATE(userlist.paytime, '%Y/%m/%d %H:%i'), '%Y/%m')")
    List<GoodCount> getMonth();

    @Select("SELECT COUNT(*) FROM userlist WHERE username = #{username} AND classtype = #{classtype} AND time = #{time}")
    Integer getTime(@Param("username")String username, @Param("classtype") String classtype, @Param("time") String time);
}
